﻿"""
功能：把上传到服务器的CSV分光数据剔除电流为NA和0值的，筛选需要的字段存入数据库
"""

# 导入需要的库

import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime

# 创建SQL server sqlalchemy数据库连接
engine = create_engine(
    "mssql+pyodbc://peins:peins8001@172.18.65.31,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)
engine2 = create_engine(
    "mssql+pyodbc://qatest:qatest@192.168.3.236,1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)

pd.set_option('display.max_columns', None)


# 定义格式转换方法
def data_to_sql(csv_path, moID, lotID, machine_id, processID, userID):
    # 读取csv格式文件，按中谱输出的csv选择需用的列数据
    print('工单', moID, 'Lot', lotID, '设备', machine_id, '工序', processID, '用户', userID, '文件', csv_path)
    if machine_id in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 22, 23, 24):
        df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=0,
                         usecols=['选择', 'No.', 'Bin号', 'Bin代号', 'VF', 'IF', 'P(W)', 'CIE-x', 'CIE-y', 'Tc',
                                  'WL.D', 'Ф(lm)', 'Ra', 'CIE-u', 'CIE-v', "CIE-u'", "CIE-v'",
                                  'WL.P', 'WL.C', 'WL.H', 'Фe(mW)', 'η(lm/W)', 'SDCM',
                                  'CRI1', 'CRI2', 'CRI3', 'CRI4', 'CRI5', 'CRI6',
                                  'CRI7', 'CRI8', 'CRI9', 'CRI10', 'CRI11',
                                  'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])
        # 字段重命名，方便数据库写入
        df = df.rename(columns={'选择': 'Notes', 'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                'VF': 'ForwardVoltage_V', 'IF': 'Current_mA', 'P(W)': 'Power_W', \
                                'CIE-x': 'CIEx', 'CIE-y': 'CIEy', 'Tc': 'CCT_K', \
                                'WL.D': 'DominantWavelength_nm', 'Ф(lm)': 'LuminousFlux_lm', 'Ra': 'Ra', \
                                'CIE-u': 'CIEu', 'CIE-v': 'CIEv', "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', 'WL.H': 'FWHM_nm', \
                                'Фe(mW)': 'RadiantFlux_mW', 'η(lm/W)': 'LumiousEfficacy_lmPerW', 'SDCM': 'SDCM', \
                                'CRI1': 'R1', 'CRI2': 'R2', 'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                'CRI7': 'R7', 'CRI8': 'R8', 'CRI9': 'R9', 'CRI10': 'R10', 'CRI11': 'R11', \
                                'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
    elif machine_id in (16, 17, 18):
        df = pd.read_excel(io=csv_path, sheet_name=0, header=0, \
                           usecols=["序号", "电流(mA)", "电压VF(V)", "功率(w)", "光通量(lm)", "光功率(mW)", "光效率(lm/w)", \
                                    "CIE-X", "CIE-Y", "BIN号", "色温(K)", "显色指数", "SDCM", "主波长(nm)", \
                                    "峰波长(nm)", "时间", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', 'R10', \
                                    'R11', 'R12', 'R13', 'R14', 'R15'])
        # 字段重命名，方便数据库写入
        df = df.rename(
            columns={"序号": 'TestNO', "电流(mA)": 'Current_mA', "电压VF(V)": 'ForwardVoltage_V', "功率(w)": 'Power_W', \
                     "光通量(lm)": 'LuminousFlux_lm', "光功率(mW)": 'RadiantFlux_mW', "光效率(lm/w)": 'LumiousEfficacy_lmPerW', \
                     "CIE-X": 'CIEx', "CIE-Y": 'CIEy', "BIN号": 'BinName', "色温(K)": 'CCT_K', "显色指数": 'Ra', \
                     "SDCM": 'SDCM', \
                     "主波长(nm)": 'DominantWavelength_nm', "峰波长(nm)": 'PeakWavelength_nm', "时间": 'TestTime', \
                     'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                     'R9': 'R9', 'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
    elif machine_id == 19:
        df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                         usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                  "CCT(K)", \
                                  "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                  'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
        df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                "Φ(lm)": 'LuminousFlux_lm', \
                                "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime', \
                                "x": 'CIEx', "y": 'CIEy', \
                                "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm', \
                                "Ra": 'Ra', 'R1': 'R1', \
                                'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                'R9': 'R9', 'R10': 'R10', \
                                'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                                '环境温度': 'TestTemperature'})
    elif machine_id == 20:
        df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                         usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                  "Tc(K)", \
                                  "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                  'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
        df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                "Φ(lm)": 'LuminousFlux_lm', \
                                "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime', \
                                "x": 'CIEx', "y": 'CIEy', \
                                "Tc(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm', \
                                "Ra": 'Ra', 'R1': 'R1', \
                                'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                'R9': 'R9', 'R10': 'R10', \
                                'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                                '环境温度': 'TestTemperature'})
    elif machine_id == 21:
        df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                         usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间", "x", "y", "CCT(K)", \
                                  "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                  'R10', 'R11', 'R12', 'R13', 'R14', 'R15'])
        df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                "Φ(lm)": 'LuminousFlux_lm', \
                                "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "时间": 'TestTime', \
                                "x": 'CIEx', "y": 'CIEy', \
                                "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm', \
                                "Ra": 'Ra', 'R1': 'R1', \
                                'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                'R9': 'R9', 'R10': 'R10', \
                                'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
    else:
        pass
    # 需要变成相应的循环吗
    df_MoID = pd.DataFrame({'MoID': [moID for i in range(0, len(df.index))]})
    df_LotID = pd.DataFrame({'LotID': [lotID for i in range(0, len(df.index))]})
    df_userID = pd.DataFrame({'UserID': [userID for i in range(0, len(df.index))]})
    df_ProcessID = pd.DataFrame({'ProcessID': [processID for i in range(0, len(df.index))]})
    df_DataStatus = pd.DataFrame({'DataStatus': [-1 for i in range(0, len(df.index))]})
    df_machine_id = pd.DataFrame({'MachineID': [machine_id for i in range(0, len(df.index))]})
    df = pd.concat([df_MoID, df_LotID, df_machine_id, df_DataStatus, df_ProcessID, df_userID, df], axis=1)
    # 这里直接增加一列
    df['U01'] = datetime.now()

    # 剔除电流为NA和0的数据
    df = df[df['Current_mA'] != 0]
    # 测试时间转成datatime格式
    df['TestTime'] = pd.to_datetime(df['TestTime'])
    # DataFrame格式整理
    colmuns = df.columns
    df['Current_mA'] = df['Current_mA'].apply(lambda x: round(float(x), 4))
    df['ForwardVoltage_V'] = df['ForwardVoltage_V'].apply(
        lambda x: round(float(x), 4))
    df['Power_W'] = df['Power_W'].apply(lambda x: round(float(x), 4))
    df['LumiousEfficacy_lmPerW'] = df['LumiousEfficacy_lmPerW'].apply(
        lambda x: round(float(x), 4))

    df['LuminousFlux_lm'] = df['LuminousFlux_lm'].apply(
        lambda x: round(float(x), 3))
    # df['RadiantFlux_mW'] = df['RadiantFlux_mW'].apply(lambda x: round(float(x), 3))

    df['CCT_K'] = df['CCT_K'].apply(lambda x: round(float(x), 1))

    df['CIEx'] = df['CIEx'].apply(lambda x: round(float(x), 6))
    df['CIEy'] = df['CIEy'].apply(lambda x: round(float(x), 6))
    if 'CIEu' in colmuns:
        df['CIEu'] = df['CIEu'].apply(lambda x: round(float(x), 6))
    if 'CIEv' in colmuns:
        df['CIEv'] = df['CIEv'].apply(lambda x: round(float(x), 6))
    if 'CIEu_1976' in colmuns:
        df['CIEu_1976'] = df['CIEu_1976'].apply(
            lambda x: round(float(x), 6))
    if 'CIEv_1976' in colmuns:
        df['CIEv_1976'] = df['CIEv_1976'].apply(
            lambda x: round(float(x), 6))

    if 'SDCM' in colmuns:
        df['SDCM'] = df['SDCM'].apply(lambda x: round(float(x), 2))
    df['Ra'] = df['Ra'].apply(lambda x: round(float(x), 2))
    df['R9'] = df['R9'].apply(lambda x: round(float(x), 2))
    if 'TestTemperature' in colmuns:
        df['TestTemperature'] = df['TestTemperature'].apply(
            lambda x: round(float(x), 2))
    df['R1'] = df['R1'].apply(lambda x: round(float(x), 2))
    df['R2'] = df['R2'].apply(lambda x: round(float(x), 2))
    df['R3'] = df['R3'].apply(lambda x: round(float(x), 2))
    df['R4'] = df['R4'].apply(lambda x: round(float(x), 2))
    df['R5'] = df['R5'].apply(lambda x: round(float(x), 2))
    df['R6'] = df['R6'].apply(lambda x: round(float(x), 2))
    df['R7'] = df['R7'].apply(lambda x: round(float(x), 2))
    df['R8'] = df['R8'].apply(lambda x: round(float(x), 2))
    df['R10'] = df['R10'].apply(lambda x: round(float(x), 2))
    df['R11'] = df['R11'].apply(lambda x: round(float(x), 2))
    df['R12'] = df['R12'].apply(lambda x: round(float(x), 2))
    df['R13'] = df['R13'].apply(lambda x: round(float(x), 2))
    df['R14'] = df['R14'].apply(lambda x: round(float(x), 2))
    df['R15'] = df['R15'].apply(lambda x: round(float(x), 2))
    df['PeakWavelength_nm'] = df['PeakWavelength_nm'].apply(
        lambda x: round(float(x), 2))
    df['DominantWavelength_nm'] = df['DominantWavelength_nm'].apply(
        lambda x: round(float(x), 2))
    if 'ComplementaryWavelength_nm' in colmuns:
        df['ComplementaryWavelength_nm'] = df[
            'ComplementaryWavelength_nm'].apply(
            lambda x: round(float(x), 2))
    if 'FWHM_nm' in colmuns:
        df['FWHM_nm'] = df['FWHM_nm'].apply(lambda x: round(float(x), 2))
    if 'ZenerVoltage_V' in colmuns:
        df['ZenerVoltage_V'] = df['ZenerVoltage_V'].apply(
            lambda x: round(float(x), 6))
    # 数据写入数据库(远方3号上传到另一个表中)
    df.to_sql('DP_test_data', engine, if_exists='append', index=None)


def info_to_sql(MoID, MachineID, UserID, Procession, UploadPath, originalFileName):
    """

    :param MoID:
    :param MachineID:
    :param UserID:
    :param Procession:
    :param UploadPath:
    :param originalFileName:
    :return:
    """
    df_s = pd.read_sql(f"select max(LotID) as LotID  from DP_test_data_info where MoID='{MoID}'", engine2)
    if not df_s.loc[0, 'LotID']:
        LotID = 1
    else:
        LotID = df_s.loc[0, 'LotID'] + 1
    df = pd.DataFrame()
    df.loc[1, 'MoID'] = MoID
    df.loc[1, 'MachineID'] = MachineID
    df.loc[1, 'LotID'] = LotID
    df.loc[1, 'LotStatus'] = 1
    df.loc[1, 'UserID'] = UserID
    df.loc[1, 'Procession'] = Procession
    df.loc[1, 'UploadPath'] = UploadPath
    df.loc[1, 'OriginalFileName'] = originalFileName
    df.loc[1, 'UploadTime'] = datetime.now()
    df.to_sql('DP_test_data_info', engine2, if_exists='append', index=False)


if __name__ == '__main__':
    # data_to_sql(csv_path='5101-20030182-1.csv', moID='5101-20030182', lotID=1, machine_id=1,processID=1,userID='10223')
    data_to_sql(csv_path='00900058-1-ZK.csv', moID='5101-11111111', lotID=1, machine_id=1, processID=1,
                userID='10901')
